import sqlite3 as sq
import os
# MY_PATH = os.path.expanduser('~\Documents\edbdata')
MY_PATH = r'C:\Users\Public\Documents\edbdata'
DB_PATH = os.path.join(MY_PATH, 'db/heavymetal.db')
CREAT_TABLE = '''
    CREATE TABLE METALDB(
    ID   INT PRIMARY KEY     NOT NULL,
    省份  TEXT    NOT NULL,
    城市  TEXT     NOT NULL,
    点位名称 TEXT  NOT NULL,
    经度  FLOAT   NOT NULL,
    纬度  FLOAT   NOT NULL,
    Cr总  FLOAT   NOT NULL,
    Cu总  FLOAT   NOT NULL,
    Pb总  FLOAT   NOT NULL,
    Zn总  FLOAT   NOT NULL,
    Mn总  FLOAT   NOT NULL,
    备注  TEXT
)'''

class MAPDATABASE():
    def __init__(self):
        # 载入数据库
        self.cur = self.connect()
        print("db初始化完毕")

    def connect(self):
        print(DB_PATH)
        if not os.path.exists(DB_PATH):
            self._con = sq.connect(DB_PATH)
            # 重写工厂函数，使得输出字典
            self._con.row_factory = dict_factory
            # 如果是新建的数据文件，则要执行创建表的操作
            cur = self._con.cursor()
            print("===没有读取到数据库文件，新建数据库===")
            cur.execute(CREAT_TABLE)
        else:
            # 否则就只是打开后返回cur
            self._con = sq.connect(DB_PATH)
            # 重写工厂函数，使得输出字典
            self._con.row_factory = dict_factory
            cur = self._con.cursor()

        return cur


    # 查
    def find(self, value, condiction=None):
        # 获取指定的数据
        # value = ['ID','经.度','纬度','点位名称',...]
        # value = ['DISTINCT 省份']
        query = '''SELECT {} FROM METALDB'''.format(value)
        # 如果传入了条件，则加入
        if condiction:
            query += condiction
        print(query)
        # 返回结果
        return self.cur.execute(query).fetchall()


    # 增
    def add(self):
        pass


    # 删
    def remove(self):
        pass


    # 改
    def change(self):
        pass


    # 关闭
    def close(self):
        self.cur.close()
        print('关闭数据库')

# 返回字典
def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

